package com.my.lucky.config;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class MySQLDataSync {

    //    源数据库
    private static final String SOURCE_URL  = "jdbc:mysql://192.168.1.2:3306/lucky?useUnicode=true&characterEncoding=utf-8";
    private static final String SOURCE_USER = "root";
    private static final String SOURCE_PASSWORD = "root";

    //    目标数据库
    private static final String TARGET_URL = "jdbc:mysql://localhost:3306/lucky?useUnicode=true&characterEncoding=utf-8";
    private static final String TARGET_USER = "root";
    private static final String TARGET_PASSWORD = "root";

    private static final int BATCH_SIZE = 1000; // 批量插入大小
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

    public static void main(String[] args) {
        try {
            String create_time = null;
            // 获取当前日期并减去一天
            LocalDate yesterday = LocalDate.now().minusDays(2);
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");  // 格式化日期
            create_time = yesterday.format(formatter);
//             System.out.println(create_time);

            // 同步指定表 "stock_hot_rank",
            syncTables(Arrays.asList("stock_limit_up","stock_limit_down", "stock_main_cost", "stock_query_record", "stocks", "wencai_keyword", "gainian_info"), create_time);
//            syncTables(Arrays.asList("stocks_60boll"), create_time);
//            syncTablesById(Arrays.asList("stocks_60boll"), 0);
            syncTablesById(Arrays.asList("sys_info", "review_info", "filter_condition", "filter_conditions_gai","stocks_60boll"), 0);
            System.out.println("数据同步完成！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void syncTables(List<String> tableNames, String create_time) throws Exception {
        try (Connection sourceConn = getSourceConnection();
             Connection targetConn = getTargetConnection()) {

            // 禁用目标数据库外键检查，避免约束冲突
            disableForeignKeyChecks(targetConn);

            for (String tableName : tableNames) {
                System.out.println("[" + sdf.format(new Date()) + "]开始同步表: " + tableName);

                // 检查表是否存在，不存在则创建
                if (!tableExists(targetConn, tableName)) {
                    createTable(sourceConn, targetConn, tableName);
                }

                // 清空目标表数据
                truncateTable(targetConn, tableName, create_time);

                // 同步数据
                syncTableData(sourceConn, targetConn, tableName, create_time);

                System.out.println("[" + sdf.format(new Date()) + "]表 " + tableName + " 同步完成");
            }

            // 启用目标数据库外键检查
            enableForeignKeyChecks(targetConn);
        }
    }

    public static void syncTablesById(List<String> tableNames, Integer id) throws Exception {
        try (Connection sourceConn = getSourceConnection();
             Connection targetConn = getTargetConnection()) {

            // 禁用目标数据库外键检查，避免约束冲突
            disableForeignKeyChecks(targetConn);

            for (String tableName : tableNames) {
                System.out.println("[" + sdf.format(new Date()) + "]开始同步表: " + tableName);

                // 检查表是否存在，不存在则创建
                if (!tableExists(targetConn, tableName)) {
                    createTable(sourceConn, targetConn, tableName);
                }

                // 清空目标表数据
                truncateTableById(targetConn, tableName, id);

                // 同步数据
                syncTableDataByID(sourceConn, targetConn, tableName, id);

                System.out.println("[" + sdf.format(new Date()) + "]表 " + tableName + " 同步完成");
            }

            // 启用目标数据库外键检查
            enableForeignKeyChecks(targetConn);
        }
    }

    private static Connection getSourceConnection() throws SQLException {
        return DriverManager.getConnection(SOURCE_URL, SOURCE_USER, SOURCE_PASSWORD);
    }

    private static Connection getTargetConnection() throws SQLException {
        return DriverManager.getConnection(TARGET_URL, TARGET_USER, TARGET_PASSWORD);
    }

    private static boolean tableExists(Connection conn, String tableName) throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        try (ResultSet tables = metaData.getTables(null, null, tableName, null)) {
            return tables.next();
        }
    }

    private static void createTable(Connection sourceConn, Connection targetConn, String tableName) throws SQLException {
        String createTableSQL = getCreateTableSQL(sourceConn, tableName);
        try (Statement stmt = targetConn.createStatement()) {
            stmt.executeUpdate(createTableSQL);
        }
    }

    private static String getCreateTableSQL(Connection conn, String tableName) throws SQLException {
        String sql = "SHOW CREATE TABLE " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getString(2);
            }
        }
        throw new SQLException("无法获取表 " + tableName + " 的创建语句");
    }

    private static void truncateTable(Connection conn, String tableName, String create_time) throws SQLException {
        String sql = "TRUNCATE TABLE " + tableName;
        if (create_time != null) {
            sql = "DELETE FROM " + tableName + " WHERE create_time>='" + create_time + "'";
        }
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        }
    }

    private static void syncTableData(Connection sourceConn, Connection targetConn, String tableName, String create_time) throws SQLException {
        String selectSQL = "SELECT * FROM " + tableName;
        if (create_time != null) {
            selectSQL += " WHERE create_time>='" + create_time + "'";
        }
        try (Statement selectStmt = sourceConn.createStatement();
             ResultSet rs = selectStmt.executeQuery(selectSQL)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 构建插入语句
            StringBuilder insertSQL = new StringBuilder();
            insertSQL.append("INSERT INTO ").append(tableName).append(" (");

            for (int i = 1; i <= columnCount; i++) {
                insertSQL.append(metaData.getColumnName(i));
                if (i < columnCount) {
                    insertSQL.append(", ");
                }
            }

            insertSQL.append(") VALUES (");
            for (int i = 1; i <= columnCount; i++) {
                insertSQL.append("?");
                if (i < columnCount) {
                    insertSQL.append(", ");
                }
            }
            insertSQL.append(")");

            // 批量插入
            try (PreparedStatement pstmt = targetConn.prepareStatement(insertSQL.toString())) {
                int batchCount = 0;

                while (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        pstmt.setObject(i, rs.getObject(i));
                    }

                    pstmt.addBatch();
                    batchCount++;

                    if (batchCount % BATCH_SIZE == 0) {
                        pstmt.executeBatch();
                        batchCount = 0;
                    }
                }

                // 执行剩余批次
                if (batchCount > 0) {
                    pstmt.executeBatch();
                }
            }
        }
    }

    private static void truncateTableById(Connection conn, String tableName, Integer id) throws SQLException {
        String sql = "TRUNCATE TABLE " + tableName;
        if (id != null) {
            sql = "DELETE FROM " + tableName + " WHERE id>=" + id + "";
        }
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        }
    }

    private static void syncTableDataByID(Connection sourceConn, Connection targetConn, String tableName, Integer id) throws SQLException {
        String selectSQL = "SELECT * FROM " + tableName;
        if (id != null) {
            selectSQL += " WHERE id>=" + id + "";
        }
        try (Statement selectStmt = sourceConn.createStatement();
             ResultSet rs = selectStmt.executeQuery(selectSQL)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 构建插入语句
            StringBuilder insertSQL = new StringBuilder();
            insertSQL.append("INSERT INTO ").append(tableName).append(" (");

            for (int i = 1; i <= columnCount; i++) {
                insertSQL.append(metaData.getColumnName(i));
                if (i < columnCount) {
                    insertSQL.append(", ");
                }
            }

            insertSQL.append(") VALUES (");
            for (int i = 1; i <= columnCount; i++) {
                insertSQL.append("?");
                if (i < columnCount) {
                    insertSQL.append(", ");
                }
            }
            insertSQL.append(")");

            // 批量插入
            try (PreparedStatement pstmt = targetConn.prepareStatement(insertSQL.toString())) {
                int batchCount = 0;

                while (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        pstmt.setObject(i, rs.getObject(i));
                    }

                    pstmt.addBatch();
                    batchCount++;

                    if (batchCount % BATCH_SIZE == 0) {
                        pstmt.executeBatch();
                        batchCount = 0;
                    }
                }

                // 执行剩余批次
                if (batchCount > 0) {
                    pstmt.executeBatch();
                }
            }
        }
    }

    private static void disableForeignKeyChecks(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("SET foreign_key_checks = 0");
        }
    }

    private static void enableForeignKeyChecks(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("SET foreign_key_checks = 1");
        }
    }
}